package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.BomPartForReport;
import com.scalar.chiptrack.operations.process.BomPartNumbersAndQuantity;
import com.scalar.chiptrack.operations.process.forms.BomPartEntryForm;
import com.scalar.chiptrack.operations.process.forms.BomSetupForm;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;

/**
 * User: Murali Krishna Yengala
 * Date: May 12, 2005
 * Time: 5:39:26 PM
 */
public class BomPartEntryDAO {
    public static ArrayList loadBomParts () throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList listMap = new ArrayList ();
        PartNumber partNumbers = null;
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.loadBoms" ) );
            rset = pstmt.executeQuery ();
            while ( rset.next () )
            {
                partNumbers = new PartNumber ();
                partNumbers.setPartNumber ( rset.getString ( "Part_Number" ) );
                partNumbers.setProductId ( rset.getInt ( "Product_ID" ) + "" );
                listMap.add ( new LabelValueBean (
                        partNumbers.getPartNumber () , partNumbers.getProductId () ) );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load bom data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return listMap;
    }


    public static BomPartEntryForm loadBomDetails ( String productId, DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        BomPartEntryForm bomForm = new BomPartEntryForm();
        int productIdInt = 0;
        if ( ( productId == null ) || ( productId.equals ( "null" ) ) )
        {
            return null;
        }
        try
        {
            productIdInt =  Integer.parseInt ( productId );    //Modified to avoid the Number Format Exception
        }
        catch( NumberFormatException ignore ){}
        
        try
        {              
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT Part_Number, P_Type, Product.ProcessStep_ID,PS_Comments,product.Is_Approved, ps_type_name FROM  Process_Step,Product WHERE Product.Product_Id = ? and Product.ProcessStep_ID = Process_Step.ProcessStep_ID" );
            pstmt.setInt ( 1 , productIdInt );
            rset = pstmt.executeQuery ();

            if ( rset.next () )
            {
                bomForm = new BomPartEntryForm ();
                bomForm.setProductId ( productIdInt );
                bomForm.setPartNbr ( rset.getString ( "Part_Number" ) );
                bomForm.setProductType ( rset.getString ( "P_Type" ) );
                bomForm.setProcessStepId ( rset.getInt ( "ProcessStep_ID" ) );
                bomForm.setDescription ( rset.getString ( "PS_Comments" ) );
                bomForm.setProcessType( rset.getString("ps_type_name"));
                //This value bools is passed as a compliment because in our system 1 = false and 0 = true;
                if( Constants.LOCATION_TYPE_DIE_BANK.equalsIgnoreCase(bomForm.getProcessType()) )
                {
                    bomForm.setVendorLocations ( VendorDAO.getDieBankLocations( bomForm.getProcessStepId () ) );
                }
                else if( Constants.LOCATION_TYPE_ASSEMBLY.equalsIgnoreCase(bomForm.getProcessType()) )
                {
                    bomForm.setVendorLocations ( VendorDAO.getAssemblyLocations( bomForm.getProcessStepId () ) );
                }
                else if( Constants.LOCATION_TYPE_FINISHED_GOODS.equalsIgnoreCase(bomForm.getProcessType()) )
                {
                    ArrayList fgVendorList = VendorDAO.getFinishedGoodsLocations( bomForm.getProcessStepId () );
                    if ( fgVendorList != null && fgVendorList.size() > 0 )
                    {
                        bomForm.setVendorLocations ( VendorDAO.getFinishedGoodsLocations( bomForm.getProcessStepId () ) );
                   }
                }
            }

            if ( rset != null )
            {
                try{rset.close ();}
                catch ( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try{pstmt.close ();}
                catch ( SQLException ignore ){}
            }
            pstmt = conn.prepareStatement ( "SELECT Parameter_Key, Parameter_Value from process_parameter where ProcessStep_ID = ?" );
            pstmt.setString( 1, ""+bomForm.getProcessStepId ());
            rset = pstmt.executeQuery();
            while( rset.next() )
            {
                String key = rset.getString( "Parameter_Key");
                if( "min qty".equalsIgnoreCase(key))
                {
                    bomForm.setMinQuantity( rset.getString("Parameter_Value"));
                }
                else if( "max qty".equalsIgnoreCase(key))
                {
                    bomForm.setMaxQuantity( rset.getString("Parameter_Value"));
                }
                else if("reorder level".equalsIgnoreCase(key))
                {
                    bomForm.setReorderLevel( rset.getString("Parameter_Value"));
                }
            }
            if ( rset != null )
            {
                try{rset.close ();}
                catch ( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try{pstmt.close ();}
                catch ( SQLException ignore ){}
            }
            pstmt = conn.prepareStatement ( "SELECT Part_Number from Product_Map_Table where ProcessStep_ID = ?" );
            pstmt.setString( 1, ""+bomForm.getProcessStepId ());
            rset = pstmt.executeQuery();
            ArrayList alternateVendorPartsList = new ArrayList();
            while( rset.next() )
            {
                alternateVendorPartsList.add( rset.getString("Part_Number") );
               //bomForm.setVendorPartNbr( rset.getString("Part_Number"));
            }
            String[] alternateVendorParts = new String[alternateVendorPartsList.size()];
            alternateVendorPartsList.toArray(alternateVendorParts);
            bomForm.setAlternateVendorParts(alternateVendorParts );
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load bom data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return bomForm;
    }


    public static ArrayList getBomPartsForAssembly( String processStepId ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        BomPartEntryForm bomForm = new BomPartEntryForm();
        ArrayList bomPartsList = null;

        if (  processStepId == null )
        {
            return null;
        }
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( "select * from  fn_bom_parts_for_bomReport(?)" );
            pstmt.setString ( 1 , processStepId  );
            rset = pstmt.executeQuery ();
            bomPartsList = new ArrayList();

            while( rset.next () )
            {
                BomPartForReport bomPart = new BomPartForReport();
                bomPart.setBomPartNo(rset.getString( "bomPartNumber"));
                bomPart.setQtyReqPerUnit( rset.getString( "qtyReqPerUnit"));
                bomPart.setQtyAvailable( rset.getString("qtyAvailable"));
                bomPartsList.add( bomPart);
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load bom data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return bomPartsList;
    }


    public static void saveBomInInventory ( DBTransaction dbTransaction , String partNumber , int processStepId , boolean insert ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            if ( insert )
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.insertBoardInventory" ) );
            else
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.board.updateBoardInventory" ) );

            pstmt.setString ( 1 , partNumber );
            pstmt.setString ( 2 , partNumber );
            pstmt.setInt ( 3 , processStepId );
            pstmt.execute ();
            status = true;
        }
        catch ( Exception sql )
        {
            sql.printStackTrace ();
            throw new ScalarException ( "Could not save Bom in product table" , sql );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( status == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException se )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException se )
                {
                }
            }
        }

    }

    public static void saveParameters(DBTransaction transaction, String processStepId, String[] paramKeys, String[] paramUnits, String[] paramValues ) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;

		try
		{
			if(transaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = transaction.getConnection();
			}
			ps = conn.prepareStatement( SqlQueryMappings.get("operations.process.deleteProcessParameters") );
			//ps = conn.prepareStatement("delete from Process_Parameter where ProcessStep_ID = ?");
			ps.setString( 1 , processStepId );
			ps.executeUpdate();
			ps.close();
			ps = null;
			if( paramKeys.length == paramValues.length )
			{
                ps = conn.prepareStatement("insert into Process_Parameter(ProcessStep_ID, Parameter_Key, Parameter_Units, Parameter_Value) values (?, ?, ?, ?)");
                for( int i =0; i < paramKeys.length; i++ )
                {
                    ps.setString(1, processStepId);
                    ps.setString(2, paramKeys[i]);
                    ps.setString(3, paramUnits[i]);
                    ps.setString(4, paramValues[i]);
                    ps.addBatch();
                }
				ps.executeBatch();
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new ScalarException("Could not save the Process Step data ", e);
		}finally{
			if (transaction == null){
				try {
					if (conn != null){
						conn.commit();
					}
				}catch (SQLException ignore) {}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){e.printStackTrace();}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){e.printStackTrace();}
			}
		}
	}

    public static void saveAlternateVendorPartNbrs(DBTransaction dbTransaction, int processStepId, String[] alternateVendorParts ) throws ScalarException
    {
		Connection conn = null;
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
		try
		{
			if(dbTransaction == null)
            {
				conn = DataSource.getConnection();
			}
            else
            {
				conn = dbTransaction.getConnection();
			}

            pstmt = conn.prepareStatement( "DELETE FROM PRODUCT_MAP_TABLE WHERE ProcessStep_ID = ?" );
            pstmt.setInt ( 1, processStepId );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            if ( alternateVendorParts != null )
            {
                for ( int i=0; i < alternateVendorParts.length; i++ )
                {
                    if ( alternateVendorParts[i] != null && alternateVendorParts[i].trim().length() > 0 )
                    {
                        pstmt2 = conn.prepareStatement ( "SELECT TOP 1 PART_NUMBER FROM PRODUCT_MAP_TABLE WHERE ProcessStep_ID = ? AND PART_NUMBER = N'" + StringUtils.formatToHandleQuote(alternateVendorParts[i].trim()) + "'" );
                        pstmt2.setInt ( 1, processStepId );
                        rset = pstmt2.executeQuery();
                        if ( rset.next() ) //Alternate Part already exists
                        {
                            throw new ScalarException ( "Alternate Vendor Part '" + new String( alternateVendorParts[i].trim().getBytes("ISO-8859-1"), "UTF-8") + "' already exists for the Part '" + rset.getString ( "PART_NUMBER" ) + "'" );
                        }
                        else
                        {
                            rset.close();
                            rset = null;
                            pstmt = conn.prepareStatement( "INSERT INTO PRODUCT_MAP_TABLE(ProcessStep_ID, Location_ID,PART_NUMBER) VALUES(?, ?,  N'"+ StringUtils.formatToHandleQuote(alternateVendorParts[i].trim()) +"')");
                            pstmt.setInt ( 1, processStepId );
                            pstmt.setInt ( 2, 0 );
                            pstmt.executeUpdate();
                            pstmt.close();
                            pstmt = null;
                        }
                    }
                }
            }

		    if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            if( e instanceof ScalarException )
            {
                throw (ScalarException)e;
            }
            else
            {
                throw new ScalarException ( "Could not save BOM alternate Parts", e );
            }
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ){}
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt2 != null )
            {
                try { pstmt2.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException  ignore ) {}
            }
        }
	}
    public static void saveVendorPartNbr(DBTransaction transaction, int processStepId, String vendorPartNbr, boolean isInsert ) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;
		try
		{
			if(transaction == null){
				conn = DataSource.getConnection();
			}else{
				conn = transaction.getConnection();
			}
            if( isInsert )
            {
			    ps = conn.prepareStatement( "insert into product_map_table ( Part_Number, Location_ID, ProcessStep_ID ) values ( ?, ?, ?)" );
            }
            else
            {
                ps = conn.prepareStatement( "update product_map_table set Part_Number = ?, Location_ID = ? where ProcessStep_ID = ?" );
            }
            ps.setString( 1, vendorPartNbr );
            ps.setInt( 2, 0);
            ps.setInt( 3 , processStepId );
			ps.executeUpdate();

		}catch(Exception e){
			e.printStackTrace();
			throw new ScalarException("Could not save the vendor Step data ", e);
		}finally{
			if (transaction == null){
				try {
					if (conn != null){
						conn.commit();
					}
				}catch (SQLException ignore) {}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){e.printStackTrace();}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){e.printStackTrace();}
			}
		}
	}

    /*************************** BOM SETUP RELATED METHODS***********************************/

    public static ArrayList loadAllBomPartNumbers ( String processType , String assyProcessStepId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList listMap = new ArrayList ();
        PartNumber partNumbers = null;
        try
        {
            conn = DataSource.getConnection ();

            String query = SqlQueryMappings.get ( "operations.bom.loadAllBomPartNumbers" );
            if(Constants.PRODUCT_TYPE_CHIPSET.equalsIgnoreCase(processType)) {
                query = "SELECT P.PART_NUMBER, P.PROCESSSTEP_ID FROM PROCESS_STEP PS\n" +
                        "INNER JOIN PRODUCT P ON PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = '"+Constants.LOCATION_TYPE_FINISHED_GOODS+"' AND P.P_Type is null and P.is_approved=0\n" +
                        "LEFT OUTER JOIN PROCESS_PARAMETER PP ON PP.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND PP.PARAMETER_KEY = '"+Constants.CHIPSET_PROCESS_PARAMETER_NAME+"' AND PP.PARAMETER_VALUE = 'false'";
                pstmt = conn.prepareStatement ( query  );
            } else {
                if ( "-1".equalsIgnoreCase(assyProcessStepId ) )
                {
                    query = query + " UNION SELECT P.Part_Number, PP.ProcessStep_ID FROM PRODUCT P, PROCESS_PARAMETER PP WHERE P.ProcessStep_ID=PP.ProcessStep_ID and PP.Parameter_Key = 'IsBomPartNumber'  AND PARAMETER_VALUE = 'TRUE' " ;
                }
                else
                {
                    query = query + " UNION SELECT P.Part_Number, PP.ProcessStep_ID FROM PRODUCT P, PROCESS_PARAMETER PP WHERE P.ProcessStep_ID=PP.ProcessStep_ID and PP.Parameter_Key = 'IsBomPartNumber'  AND PARAMETER_VALUE = 'TRUE' AND PP.PROCESSSTEP_ID = (SELECT ProcessStep_id FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( ?, 0 ) WHERE PS_TYPE = ? ) " ;
                }
                pstmt = conn.prepareStatement ( query  );
                pstmt.setString(1, processType );
                if ( !"-1".equalsIgnoreCase(assyProcessStepId ) )
                {
                    pstmt.setString(2, assyProcessStepId );
                    pstmt.setString(3, Constants.LOCATION_TYPE_DIE_BANK );
                }
            }
            
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                partNumbers = new PartNumber ();
                partNumbers.setPartNumber ( rset.getString ( 1 ) );
                partNumbers.setProcessStepId ( rset.getString( 2 ) );
                listMap.add ( new LabelValueBean (
                        partNumbers.getPartNumber () , partNumbers.getProcessStepId () ) );
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load boards data from database" , sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return listMap;
    }

    public static void saveBomPartNumbers( BomSetupForm form ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		BomPartNumbersAndQuantity bomPartNumbersAndQuantity= null;
		boolean success = false;
		try
		{
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.bom.deleteBomPartNumbers" ) );
		    pstmt.setInt( 1, form.getBomSetup().getAssemblyProcessStepId() );
            pstmt.executeUpdate();

			pstmt.close();
			pstmt = null;

			for( int i = 0; i < form.getBomSetup().getBomPartNumbersAndQuantitylist().size(); i++ )
			{
				bomPartNumbersAndQuantity = (BomPartNumbersAndQuantity) form.getBomSetup().getBomPartNumbersAndQuantitylist().get( i );

                if( !"".equalsIgnoreCase( bomPartNumbersAndQuantity.getQuantity() ) )
                {
                    pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.bom.insertBomPartNumbers" ) );
                    pstmt.setInt    ( 1, form.getBomSetup().getAssemblyProcessStepId() );
                    pstmt.setInt    ( 2, bomPartNumbersAndQuantity.getBomProcessStepId() );
                    pstmt.setString ( 3, bomPartNumbersAndQuantity.getQuantity() );
                    pstmt.setInt ( 4, bomPartNumbersAndQuantity.isPrimaryPartNumber() ? 0 : 1);

                    pstmt.executeUpdate();
                    pstmt.clearParameters();
                    pstmt.close();
                }
			}
			success = true;
		}
		catch( SQLException se )
		{
			se.printStackTrace();
			throw new ScalarException ( "Could not update the Bom setup" );
		}
		catch ( ScalarException e )
		{
			e.printStackTrace();
			throw new ScalarException ( "Could not update the Bom setup" );
		}
		finally
		{
			try
			{
				if ( conn != null )
				{
					if( success == true )
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e )
			{
				System.out.println( "SQLException while fetching process details" );
			}
		}
    }
    public static void deleteBomPartNumbers( BomSetupForm form ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		boolean success = false;
		try
		{
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.bom.deleteBomPartNumbers" ) );
		    pstmt.setInt( 1, form.getBomSetup().getAssemblyProcessStepId() );
            pstmt.executeUpdate();

			pstmt.close();
			pstmt = null;

			success = true;
		}
		catch( SQLException se )
		{
			se.printStackTrace();
			throw new ScalarException ( "Could not delete the Bom setup" );
		}

		finally
		{
			try
			{
				if ( conn != null )
				{
					if( success == true )
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
				if ( pstmt != null ) { pstmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e )
			{
				System.out.println( "SQLException while fetching process details" );
			}
		}
    }

    public static void deleteBomPartNumbers(int processStepId, int bomProcessStepId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("DELETE FROM Assembly_BOM_Setup_Table WHERE ASSEMBLY_PROCESSSTEP_ID = ? and BOM_PROCESSSTEP_ID = ?");
            pstmt.setInt(1, processStepId);
            pstmt.setInt(2, bomProcessStepId);
            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;
            conn.commit();
            success = true;
        }
        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException("Could not delete the Bom setup");
        }

        finally {
            try {
                if (conn != null) {
                    if (!success) {
                        conn.rollback();
                    }
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                System.out.println("SQLException while fetching process details");
            }
        }
    }
    public static void loadBomPartNumbers( BomSetupForm form ) throws ScalarException
    {
		Connection conn 		= null;
		PreparedStatement psmt = null;
        ResultSet rs = null;
        ArrayList bomPartNumbersAndQuantityList = new ArrayList();
        int assemblyProductId = form.getBomSetup().getAssemblyProcessStepId();
		boolean success = false;
		try
        {
            conn = DataSource.getConnection ();
            psmt = conn.prepareStatement ( "SELECT BOM_PROCESSSTEP_ID, QTY, IS_PRIMARY_PART FROM Assembly_BOM_Setup_Table WHERE ASSEMBLY_PROCESSSTEP_ID = ?" );
            psmt.setInt ( 1 , assemblyProductId );
            rs = psmt.executeQuery ();
            while ( rs.next () )
            {
                BomPartNumbersAndQuantity bomPartNumbersAndQuantity = new BomPartNumbersAndQuantity ();
                bomPartNumbersAndQuantity.setBomProcessStepId( rs.getInt ( "BOM_PROCESSSTEP_ID" ) );
                bomPartNumbersAndQuantity.setQuantity( rs.getString ( "QTY" ) );
                bomPartNumbersAndQuantity.setPrimaryPartNumber( rs.getInt("IS_PRIMARY_PART") == 0 );

                bomPartNumbersAndQuantityList.add ( bomPartNumbersAndQuantity );

            }
            form.getBomSetup().setBomPartNumbersAndQuantitylist( bomPartNumbersAndQuantityList );
        }
        catch ( Exception sqle )
        {
            System.out.println ( sqle.getMessage () );
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Bom Documents from database" , sqle );
        }
		finally
		{
			try
			{
				if ( conn != null )
				{
					if( success == true )
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
				if ( psmt != null ) { psmt.close(); }
				if ( conn != null  ) { conn.close(); }
			}catch( SQLException e )
			{
				System.out.println( "SQLException while fetching process details" );
			}
		}
    }

    public static ArrayList loadBomPartNumbersInventory( int productID ) throws ScalarException
    {
        ArrayList bomPartNumberList = new ArrayList();
        Connection conn             = null;
        PreparedStatement ps        = null;
        ResultSet rs                = null;
        BomPartNumbersAndQuantity bomPartNumbers = null;
        int quantityIn                   = 0;

        try
        {
            conn = DataSource.getConnection();
            ps   = conn.prepareStatement( "{CALL SP_BOM_INVENTORY_REPORT ( ?, ? ) }" );
            ps.setInt( 1, productID );
            ps.setString( 2, "0" );
            rs = ps.executeQuery();
            while( rs.next() )
            {
                quantityIn = rs.getInt( "Qty_In" );
                //if( quantityIn > 0 )
                //{
                bomPartNumbers = new BomPartNumbersAndQuantity();
                bomPartNumbers.setBomPartNumber ( rs.getString( "Part_Number" ) );
                bomPartNumbers.setVendorName    ( rs.getString( "Location_Name" ) );
                bomPartNumbers.setQuantity      ( quantityIn + "" );
                bomPartNumberList.add( bomPartNumbers );
                //}
            }
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Bom part numbers inventory");
        }
        finally
        {
            try
            {
                if( rs != null ) { rs.close(); }
                if( ps != null ) { ps.close(); }
                if( conn != null ) {conn.close(); }

            }
            catch( SQLException sqle ) { System.out.println( "SQLException while Bom part numbers inventory" ); }
        }
        return bomPartNumberList;
    }

}